<?php

namespace app\modules\user\controllers;

use app\models\BatchSetAttrForm;
use app\models\Card;
use app\models\CardAttr;
use app\models\CardAttrValue;
use app\models\CardBill;
use app\models\CardReplace;
use app\models\CardStatusChange;
use app\models\jasper\Api;
use app\models\KeyMap;
use app\models\Package;
use app\models\User;
use app\models\UserConfig;
use app\models\UserLog;
use app\models\UserBill;
use app\models\UserPackage;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use Yii;
use yii\base\InvalidConfigException;
use yii\data\Pagination;
use yii\db\Expression;
use yii\helpers\FileHelper;
use yii\helpers\Url;
use yii\web\BadRequestHttpException;
use yii\web\NotFoundHttpException;
use yii\web\ServerErrorHttpException;
use yii\web\UploadedFile;

/**
 * 物联网卡管理
 * Class CardController
 * @package app\modules\user\controllers
 */
class CardController extends BaseController
{
    /**
     * 卡列表
     * @return string
     * @throws BadRequestHttpException
     */
    public function actionList()
    {
        $query = Card::find()->alias('card');
        $query->andWhere(['card.uid' => $this->user->id]);
        $query->andFilterWhere(['card.status' => $this->get('search_status')]);
        $query->andFilterWhere(['card.iccid' => $this->get('search_iccid')]);
        $query->andFilterWhere(['card.msisdn' => $this->get('search_msisdn')]);
        $query->andFilterWhere(['card.package_id' => $this->get('search_package_id')]);
        $searchAttr = $this->get('search_attr');
        if (!empty($searchAttr) && is_array($searchAttr)) {
            foreach ($searchAttr as $searchAttrId => $searchAttrValue) {
                if (empty($searchAttrValue) && $searchAttrValue == null) {
                    continue;
                }
                $cardAttr = CardAttr::findOne(['id' => $searchAttrId, 'uid' => Yii::$app->user->id]);
                if (empty($cardAttr)) {
                    throw new BadRequestHttpException('没有找到自定义属性。');
                }
                $valueList = CardAttrValue::find()
                    ->asArray()
                    ->select('card_id')
                    ->andWhere(['uid' => Yii::$app->user->id])
                    ->andWhere(['attr_id' => $searchAttrId])
                    ->andWhere(['value' => $searchAttrValue])
                    ->all();
                $query->andWhere(['card.id' => array_column($valueList, 'card_id')]);
            }
        }
        $searchEndDayAlert = $this->get('search_end_day_alert');
        if (!empty($searchEndDayAlert)) { // 到期提醒的卡
            $query->andWhere(['>', 'card.term_end_time', 0]);
            $query->andWhere(['<=', 'card.term_end_time', time() + $searchEndDayAlert * 86400]);
        }
        $searchOverload = $this->get('search_overload', 0) == 1;
        if ($searchOverload) {
            $query->joinWith('package package');
            $query->andWhere(new Expression('card.data > package.data'));
        }
        $pagination = new Pagination(['totalCount' => $query->count()]);
        $sortName = $this->get('sort_name');
        $sortOrder = $this->get('sort_order', 'DESC');
        if (empty($sortName)) {
            $sortName = UserConfig::getConfig($this->user->id, 'card_list_sort_name', 'card.refresh_time');
        } else {
            UserConfig::setConfig($this->user->id, 'card_list_sort_name', $sortName);
        }
        if (!empty($sortName) && !in_array($sortName, [
            'card.id', 'card.iccid', 'card.msisdn',
            'card.refresh_time', 'card.data',
            'card.term_end_time', 'card.attr_update_time',
        ])) {
            throw new BadRequestHttpException('参数错误。');
        }
        if (!empty($sortOrder) && !in_array($sortOrder, ['ASC', 'DESC'])) {
            throw new BadRequestHttpException('参数错误。');
        }
        $query->orderBy([
            $sortName => ['ASC' => SORT_ASC, 'DESC' => SORT_DESC][$sortOrder],
        ]);
        /** @var CardAttr[] $attrList */
        $attrList = CardAttr::find()->andWhere(['uid' => $this->user->id])->andWhere(['show_in_list' => 1])->all();
        if ($this->get('export') == 'excel') { // 导出Excel文件
            try {
                $excel = new Spreadsheet();
                $sheet = $excel->setActiveSheetIndex(0);
                foreach ([
                             '编号',
                             'ICCID',
                             'MSISDN',
                             'IMSI',
                             '套餐',
                             '套餐有效期',
                             '套餐结束时间',
                             '数据用量（本月）',
                             '开关机',
                             '激活时间',
                             '状态',
                         ] as $index => $title) {
                    $sheet->setCellValue(chr(65 + $index) . '1', $title);
                }
                foreach ($attrList as $index => $attr) {
                    $sheet->setCellValue(chr(65 + $index + 11) . '1', $attr->name);
                }
                $sheet->getStyle('A1:Z1')->applyFromArray(['font' => ['bold' => true], 'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]]);
                $r = 2;
                /** @var Card $card */
                foreach ($query->each() as $card) {
                    $sheet->setCellValueExplicitByColumnAndRow(1, $r, $card->id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(2, $r, $card->iccid, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(3, $r, $card->msisdn, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(4, $r, $card->imsi, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(5, $r, empty($card->package_id) ? '' : $card->package->name, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(6, $r, $card->term_length . KeyMap::getValue('card_term_length_unit', $card->term_length_unit), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(7, $r, Yii::$app->formatter->asDatetime($card->term_end_time), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(8, $r, Yii::$app->formatter->asShortSize($card->data), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(9, $r, $card->on_off, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(10, $r, Yii::$app->formatter->asDatetime($card->active_time), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(11, $r, KeyMap::getValue('card_status', $card->status), DataType::TYPE_STRING);
                    foreach ($attrList as $index => $attr) {
                        $attrValue = CardAttrValue::findOne(['uid' => $card->uid, 'attr_id' => $attr->id, 'card_id' => $card->id]);
                        if (!empty($attrValue)) {
                            $sheet->setCellValueExplicitByColumnAndRow($index + 12, $r, $attr->getHtml($attrValue->value), DataType::TYPE_STRING);
                        }
                    }
                    $r++;
                }
                $sheet->freezePane('A2');
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="card_' . date('Y-m-d') . '.xls"');
                header('Cache-Control: max-age=0');
                $excelWriter = IOFactory::createWriter($excel, 'Xls');
                $excelWriter->save('php://output');
            } catch (\Exception $e) {
                Yii::error('导出Excel文件时出错：' . $e->getMessage());
                Yii::error($e->getTraceAsString());
                echo $e->getMessage();
            }
            die();
        }
        if ($this->get('export') == 'csv') { // 导出CSV文件
            header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
            header('Content-Disposition: attachment;filename="card_' . date('Y-m-d') . '.csv"');
            header('Cache-Control: max-age=0');
            echo chr(0xEF).chr(0xBB).chr(0xBF);
            echo "编号,ICCID,MSISDN,IMSI,套餐,套餐有效期,套餐结束时间,数据用量（本月）（B）,开关机,激活时间,状态";
            foreach ($attrList as $index => $attr) {
                echo ',', $attr->name;
            }
            echo "\r\n";
            /** @var Card $card */
            foreach ($query->each() as $card) {
                echo $card->id, ',';
                echo $card->iccid, ',';
                echo $card->msisdn, ',';
                echo $card->imsi, ',';
                echo empty($card->package_id) ? '' : $card->package->name, ',';
                echo $card->term_length, KeyMap::getValue('card_term_length_unit', $card->term_length_unit), ',';
                try {
                    echo Yii::$app->formatter->asDatetime($card->term_end_time), ',';
                } catch (InvalidConfigException $e) {
                    echo ',';
                }
                echo $card->data, ',';
                echo $card->on_off, ',';
                try {
                    echo Yii::$app->formatter->asDatetime($card->active_time), ',';
                } catch (InvalidConfigException $e) {
                    echo ',';
                }
                echo KeyMap::getValue('card_status', $card->status);
                foreach ($attrList as $index => $attr) {
                    $attrValue = CardAttrValue::findOne(['uid' => $card->uid, 'attr_id' => $attr->id, 'card_id' => $card->id]);
                    if (!empty($attrValue)) {
                        echo ',', $attr->getHtml($attrValue->value);
                    }
                }
                echo "\r\n";
            }
            die();
        }
        $query->offset($pagination->offset)->limit($pagination->limit);
        $cardList = $query->all();
        return $this->render('card_list', [
            'user' => $this->user->identity,
            'attrList' => $attrList,
            'cardList' => $cardList,
            'pagination' => $pagination,
        ]);
    }

    /**
     * 到期提醒卡列表
     * @return string
     * @throws BadRequestHttpException
     */
    public function actionListEndDayAlert()
    {
        return $this->actionList();
    }

    /**
     * 超流量卡列表
     * @return string
     * @throws BadRequestHttpException
     */
    public function actionListOverload()
    {
        return $this->actionList();
    }

    /**
     * 刷新卡信息AJAX接口
     * @return array
     */
    public function actionRefresh()
    {
        $id = $this->get('id');
        $card = Card::findOne(['id' => $id, 'uid' => $this->user->id]);
        if (empty($card)) {
            return ['error' => 'SERVER', 'message' => '没有找到卡信息。'];
        }
        try {
            $card->refreshBasic();
            return [];
        } catch (\Exception $e) {
            return ['error' => 'SERVER', 'message' => $e->getMessage()];
        }
    }

    /**
     * 强制设备断开网络，使其重新注册。AJAX接口
     * @return array
     */
    public function actionRestart()
    {
        $id = $this->get('id');
        $card = Card::findOne(['id' => $id, 'uid' => $this->user->id]);
        if (empty($card)) {
            return ['error' => 'SERVER', 'message' => '没有找到卡信息。'];
        }
        if ($card->api_id == 1) {
            $api = new Api($card->isp->data);
            try {
                $resp = $api->SendCancelLocation($card->iccid);
                UserLog::info($this->user->id, '重启卡[id=' . $card->id . ']。');
                return ['resp'=>$resp];
            } catch (\Exception $e) {
                return ['error' => 'SERVER', 'message' => $e->getMessage()];
            }
        } else {
            return ['error' => 'SERVER', 'message' => '不支持此操作。'];
        }
    }

    /**
     * 设置卡状态AJAX接口
     * @return array
     */
    public function actionSetCardStatus()
    {
        $id = $this->get('id');
        $card = Card::findOne(['id' => $id, 'uid' => $this->user->id]);
        if (empty($card)) {
            return ['error' => 'SERVER', 'message' => '没有找到卡信息。'];
        }
        $status = $this->get('status');
        if (!in_array($status, Card::STATUS_TRANSFORM_MAP[$card->status])) {
            return ['error' => 'SERVER', 'message' => '无法将卡设置为：' . KeyMap::getValue('card_status', $status)];
        }
        try {
            $card->setStatus($status);
            UserLog::info($this->user->id, '设置卡[id=' . $card->id . ']状态[' . $status . ']。');
            CardStatusChange::info($id, $card->status, $status, 3, 'uid=' . $this->user->id);
            return [];
        } catch (\Exception $e) {
            return ['error' => 'SERVER', 'message' => $e->getMessage()];
        }
    }

    /**
     * 卡详情
     * @return string
     * @throws NotFoundHttpException
     */
    public function actionView()
    {
        $id = $this->get('id');
        $card = Card::findOne(['id' => $id, 'uid' => $this->user->id]);
        if (empty($card)) {
            throw new NotFoundHttpException('没有找到卡信息。');
        }
        return $this->render('card_view', [
            'card' => $card,
        ]);
    }

    /**
     * 卡编辑
     * @return string
     * @throws NotFoundHttpException
     * @throws \yii\base\Exception
     */
    public function actionEdit()
    {
        $id = $this->get('id');
        $card = Card::findOne(['id' => $id, 'uid' => $this->user->id]);
        if (empty($card)) {
            throw new NotFoundHttpException('没有找到卡信息。');
        }
        if ($card->load($this->post()) && $card->save()) {
            /** @var CardAttr $attr */
            foreach (CardAttr::find()
                ->andWhere(['uid' => Yii::$app->user->id])
                ->andWhere(['status' => CardAttr::STATUS_OK])
                ->orderBy('sort DESC, id ASC')
                ->each() as $attr) {
                $attrValue = CardAttrValue::findOne(['uid' => Yii::$app->user->id, 'attr_id' => $attr->id, 'card_id' => $card->id]);
                if (empty($attrValue)) {
                    $attrValue = new CardAttrValue();
                    $attrValue->uid = Yii::$app->user->id;
                    $attrValue->card_id = $card->id;
                    $attrValue->attr_id = $attr->id;
                }
                switch ($attr->type) {
                    case CardAttr::TYPE_FILE:
                    case CardAttr::TYPE_IMAGE:
                        $file = UploadedFile::getInstanceByName('CardAttrValue_' . $attr->id);
                        if (!empty($file)) {
                            $path = Yii::getAlias('@webroot') . Yii::$app->params['upload_url'];
                            $uri = 'user_' . Yii::$app->user->id . '/';
                            FileHelper::createDirectory($path . $uri);
                            $uri .= 'card_' . $card->id . '-attr_' . $attr->id . '.' . $file->extension;
                            $file->saveAs($path . $uri);
                            $attrValue->value = $uri;
                        }
                        break;
                    case CardAttr::TYPE_PASSWORD:
                    case CardAttr::TYPE_TEXT:
                    case CardAttr::TYPE_MULTI_TEXT:
                    case CardAttr::TYPE_RICH_TEXT:
                    case CardAttr::TYPE_NUMBER:
                    case CardAttr::TYPE_EMAIL:
                    case CardAttr::TYPE_URL:
                    case CardAttr::TYPE_RADIO:
                    case CardAttr::TYPE_CHECKBOX:
                    case CardAttr::TYPE_DATETIME:
                    case CardAttr::TYPE_DATE:
                    case CardAttr::TYPE_TIME:
                    case CardAttr::TYPE_COLOR:
                    default:
                        if ($this->post('CardAttrValue_' . $attr->id) !== null) {
                            $value = $this->post('CardAttrValue_' . $attr->id);
                            if (is_array($value)) {
                                $value = implode(PHP_EOL, $value);
                            }
                            $attrValue->value = $value;
                        }
                }
                if (!$attrValue->save()) {
                    throw new ServerErrorHttpException('无法保存卡属性【' . $attr->name . '】值【' . $attrValue->value . '】，错误【' . implode('<br />', $attrValue->getErrorSummary(true)) . '】。');
                }
                $card->updateAttributes(['attr_update_time' => time()]);
            }
            UserLog::info($this->user->id, '保存卡[id=' . $card->id . ']。', $this->post());
            Yii::$app->session->addFlash('success', '数据已保存。');
            Yii::$app->session->setFlash('redirect', json_encode([
                'url' => Url::to(['/user/card/list']),
                'txt' => '卡列表'
            ]));
        }
        return $this->render('card_edit', [
            'card' => $card,
        ]);
    }

    /**
     * 属性列表
     * @return string
     */
    public function actionAttr()
    {
        $query = CardAttr::find();
        $query->andWhere(['uid' => Yii::$app->user->id]);
        $query->andWhere(['status' => CardAttr::STATUS_OK]);
        $query->orderBy('sort DESC, id ASC');
        $attrList = $query->all();
        return $this->render('attr_list', [
            'attrList' => $attrList,
        ]);
    }

    /**
     * 添加/修改属性
     * @return string
     * @throws NotFoundHttpException
     */
    public function actionAttrEdit()
    {
        $id = $this->get('id');
        if (!empty($id)) {
            $attr = CardAttr::findOne(['id' => $id, 'uid' => Yii::$app->user->id]);
            if (empty($attr)) {
                throw new NotFoundHttpException('没有找到属性信息。');
            }
        } else {
            $attr = new CardAttr();
            $attr->uid = Yii::$app->user->id;
            $attr->status = CardAttr::STATUS_OK;
        }
        if ($attr->load($this->post()) && $attr->save()) {
            UserLog::info($this->user->id, '保存卡属性。', $this->post());
            Yii::$app->session->addFlash('success', '数据已保存。');
            Yii::$app->session->setFlash('redirect', json_encode([
                'url' => Url::to(['/user/card/attr']),
                'txt' => '属性列表'
            ]));
        }
        return $this->render('attr_edit', [
            'attr' => $attr,
        ]);
    }

    /**
     * 删除卡属性AJAX接口
     * @return array
     */
    public function actionAttrDelete()
    {
        $id = $this->get('id');
        $attr = CardAttr::findOne(['id' => $id, 'uid' => Yii::$app->user->id]);
        if (empty($attr)) {
            return ['error' => 'NOT_FOUND', 'message' => '没有找到属性。'];
        }
        $attr->status = CardAttr::STATUS_DEL;
        if (!$attr->save()) {
            return ['error' => 'NOT_FOUND', 'message' => '无法保存状态。', 'errors' => $attr->errors];
        }
        UserLog::info($this->user->id, '删除卡属性[id=' . $attr->id . ']。');
        return [];
    }

    /**
     * 卡替换列表
     * @return string
     */
    public function actionReplaceList()
    {
        $query = CardReplace::find()->alias('replace');
        $query->joinWith('subUser subUser');
        $query->andWhere(['subUser.uid' => Yii::$app->user->id]);
        $pagination = new Pagination(['totalCount' => $query->count()]);
        $query->offset($pagination->offset)->limit($pagination->limit);
        $query->orderBy('status ASC, id DESC');
        $replaceList = $query->all();
        return $this->render('replace_list', [
            'replaceList' => $replaceList,
            'pagination' => $pagination,
        ]);
    }

    /**
     * 卡替换通过AJAX接口
     * @return array
     */
    public function actionAcceptReplace()
    {
        $id = $this->get('id');
        $replace = CardReplace::findOne(['id' => $id]);
        if (empty($replace) || $replace->subUser->uid != $this->user->id) {
            return ['error' => 'NOT_FOUND', '没有找到替换信息。'];
        }
        if ($replace->status != CardReplace::STATUS_WAIT) {
            return ['error' => 'STATUS', '状态错误。'];
        }
        /** @var CardAttrValue $fromAttrValue */
        foreach (CardAttrValue::find()->andWhere(['card_id' => $replace->from_card_id])->each() as $fromAttrValue) {
            $toAttrValue = CardAttrValue::findOne(['uid' => $replace->toCard->uid, 'card_id' => $replace->to_card_id, 'attr_id' => $fromAttrValue->attr_id]);
            if (empty($toAttrValue)) {
                $toAttrValue = new CardAttrValue();
                $toAttrValue->uid = $replace->toCard->uid;
                $toAttrValue->card_id = $fromAttrValue->card_id;
                $toAttrValue->attr_id = $fromAttrValue->attr_id;
            }
            $toAttrValue->value = $fromAttrValue->value;
            $toAttrValue->save();
            $replace->toCard->updateAttributes(['attr_update_time' => time()]);
        }
        $replace->updateAttributes(['status' => CardReplace::STATUS_COMPLETE, 'verify_time' => time()]);
        UserLog::info($this->user->id, '通过卡替换申请[id=' . $replace->id . ']。');
        return [];
    }

    /**
     * 卡替换拒绝AJAX接口
     * @return array
     */
    public function actionRejectReplace()
    {
        $id = $this->get('id');
        $replace = CardReplace::findOne(['id' => $id]);
        if (empty($replace) || $replace->subUser->uid != $this->user->id) {
            return ['error' => 'NOT_FOUND', '没有找到替换信息。'];
        }
        if ($replace->status != CardReplace::STATUS_WAIT) {
            return ['error' => 'STATUS', '状态错误。'];
        }
        $replace->updateAttributes(['status' => CardReplace::STATUS_REJECTED, 'verify_time' => time()]);
        UserLog::info($this->user->id, '拒绝卡替换申请[id=' . $replace->id . ']。');
        return [];
    }

    /**
     * 批量设置卡属性
     * @return string
     * @throws \yii\base\Exception
     */
    public function actionBatchSetAttr()
    {
        $model = new BatchSetAttrForm();
        if ($model->load($this->post())) {
            $valueMap = [];
            /** @var CardAttr $attr */
            foreach (CardAttr::find()
                         ->andWhere(['uid' => Yii::$app->user->id])
                         ->andWhere(['status' => CardAttr::STATUS_OK])
                         ->orderBy('sort DESC, id ASC')
                         ->each() as $attr) {
                switch ($attr->type) {
                    case CardAttr::TYPE_FILE:
                    case CardAttr::TYPE_IMAGE:
                        $file = UploadedFile::getInstanceByName('CardAttrValue_' . $attr->id);
                        if (!empty($file)) {
                            $path = Yii::getAlias('@webroot') . Yii::$app->params['upload_url'];
                            $uri = 'user_' . Yii::$app->user->id . '/';
                            FileHelper::createDirectory($path . $uri);
                            $uri .= 'card_0-attr_' . $attr->id . '.' . $file->extension;
                            $file->saveAs($path . $uri);
                            $valueMap[$attr->id] = $uri;
                        }
                        break;
                    case CardAttr::TYPE_PASSWORD:
                    case CardAttr::TYPE_TEXT:
                    case CardAttr::TYPE_MULTI_TEXT:
                    case CardAttr::TYPE_RICH_TEXT:
                    case CardAttr::TYPE_NUMBER:
                    case CardAttr::TYPE_EMAIL:
                    case CardAttr::TYPE_URL:
                    case CardAttr::TYPE_RADIO:
                    case CardAttr::TYPE_CHECKBOX:
                    case CardAttr::TYPE_DATETIME:
                    case CardAttr::TYPE_DATE:
                    case CardAttr::TYPE_TIME:
                    case CardAttr::TYPE_COLOR:
                    default:
                        if ($this->post('CardAttrValue_' . $attr->id) !== null) {
                            $value = $this->post('CardAttrValue_' . $attr->id);
                            if (!empty($value)) {
                                if (is_array($value)) {
                                    $value = implode(PHP_EOL, $value);
                                }
                                $valueMap[$attr->id] = $value;
                            }
                        }
                }
            }

            if ($model->setAttrValue($this->user->id, $valueMap)) {
                UserLog::info($this->user->id, '批量保存卡属性。', $this->post());
                Yii::$app->session->addFlash('success', '数据已保存。');
            }
        }
        return $this->render('batch_set_attr', [
            'model' => $model,
        ]);
    }
    
    /**
     * 用户账单列表
     * @return string
     */
    public function actionUserBillList()
    {
        $query = UserBill::find();
        $query->andWhere(['uid' => $this->user->id]);
        $query->andWhere(['status' => UserBill::STATUS_PUBLISH]);
        $pagination = new Pagination(['totalCount' => $query->count()]);
        $query->orderBy('month DESC');
        $query->offset($pagination->offset)->limit($pagination->limit);
        $userBillList = $query->all();
        
        return $this->render('user_bill_list', [
            'user' => $this->user->identity,
            'userBillList' => $userBillList,
            'pagination' => $pagination,
        ]);
    }
    
    
    /**
     * 账单列表
     * @return string
     */
    public function actionBillList()
    {
        $searchMonth = $this->get('search_month');
        $userbill = UserBill::findOne(['uid' => $this->user->id, 'status' => UserBill::STATUS_PUBLISH, 'package_id' => $this->get('search_package_id'), 'month' => preg_replace('/\D/', '', $searchMonth)]);
        if (empty($userbill)) {
            throw new BadRequestHttpException('没有找到对应套餐账单信息。');
        }
        $query = CardBill::find()->alias('bill');
        $query->joinWith('card card');
        $query->andWhere(['user_id' => $this->user->id]);
        if (!empty($searchMonth)) {
            $query->andWhere(['bill.month' => preg_replace('/\D/', '', $searchMonth)]);
        }
        $query->andFilterWhere(['card.package_id' => $this->get('search_package_id')]);
        $searchIccid = $this->get('search_iccid');
        if (!empty($searchIccid)) {
            $query->joinWith('card card')->andWhere(['card.iccid' => $searchIccid]);
        }
        $attrList = CardAttr::find()->andWhere(['uid' => $this->user->id])->andWhere(['show_in_list' => 1])->all();
        if ($this->get('export') == 'excel') { // 导出Excel文件
            try {
                $excel = new Spreadsheet();
                /** @var User $user */
                $user = User::findOne(['username' => $this->get('search_username')]);
                /** @var CardAttr[] $attrList 卡自定义属性列表 */
                $attrList = CardAttr::find()->andWhere(['uid' => $user->id])->all();
                /** @var Package[] $packageList 套餐列表 */
                $packageList = [];
                /** @var array $cardAmount 不同套餐计费数量 */
                $cardAmount = [];
                $query->orderBy('id DESC');
                /** @var CardBill $bill */
                foreach ($query->each() as $bill) {
                    if (!isset($packageList[$bill->package_id])) {
                        $packageList[$bill->package_id] = $bill->package;
                        $cardAmount[$bill->package_id] = 0;
                    }
                    $cardAmount[$bill->package_id] += 1;
                }
                $month = $this->get('search_month');
                $sheet = $excel->setActiveSheetIndex(0);
                $sheet->setCellValueExplicitByColumnAndRow(1, 1, '用户名称', DataType::TYPE_STRING);
                $sheet->setCellValueExplicitByColumnAndRow(2, 1, '套餐', DataType::TYPE_STRING);
                $sheet->setCellValueExplicitByColumnAndRow(3, 1, '月份', DataType::TYPE_STRING);
                $sheet->setCellValueExplicitByColumnAndRow(4, 1, '计费数量（张）', DataType::TYPE_STRING);
                $sheet->setCellValueExplicitByColumnAndRow(5, 1, '单价（元）', DataType::TYPE_STRING);
                $sheet->setCellValueExplicitByColumnAndRow(6, 1, '金额（元）', DataType::TYPE_STRING);
                $i = 0;
                foreach ($packageList as $package) {
                    $sheet->setCellValueExplicitByColumnAndRow(1, 2 + $i, $user->username, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(2, 2 + $i, $package->name, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(3, 2 + $i, $month, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(4, 2 + $i, $cardAmount[$package->id], DataType::TYPE_NUMERIC);
                    $userPackage = UserPackage::findOne(['uid' => $user->id, 'package_id' => $package->id]);
                    $sheet->setCellValueExplicitByColumnAndRow(5, 2 + $i, $userPackage->cost_price, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(6, 2 + $i, $userPackage->cost_price * $cardAmount[$package->id], DataType::TYPE_NUMERIC);
                    $i += 1;
                }
                if (count($packageList) > 1) {
                    $sheet->mergeCellsByColumnAndRow(1, 2, 1, 1 + count($packageList));
                }
                $sheet->getColumnDimension('A')->setWidth(15);
                $sheet->getColumnDimension('B')->setWidth(20);
                $sheet->getColumnDimension('C')->setWidth(10);
                $sheet->getColumnDimension('D')->setWidth(10);
                $sheet->getColumnDimension('E')->setWidth(10);
                $sheet->getColumnDimension('F')->setWidth(10);
                $sheet->getDefaultRowDimension()->setRowHeight(30);
                $sheet->freezePane('A2');
                $sheet->setTitle('总账单');
                foreach ($packageList as $package) {
                    $userPackage = UserPackage::findOne(['uid' => $user->id, 'package_id' => $package->id]);
                    $sheet = $excel->createSheet();
                    $sheet->freezePane('A2');
                    $sheet->setTitle($package->name);
                    $sheet->setCellValueExplicitByColumnAndRow(1, 1, 'ICCID', DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(2, 1, 'MSISDN', DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(3, 1, '客户', DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(4, 1, '套餐', DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(5, 1, '费用', DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(6, 1, '流量（MB）', DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(7, 1, '短信（条）', DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(8, 1, '语音（分钟）', DataType::TYPE_STRING);
                    $sheet->getColumnDimension('A')->setWidth(25);
                    $sheet->getColumnDimension('B')->setWidth(15);
                    $sheet->getColumnDimension('C')->setWidth(20);
                    $sheet->getColumnDimension('D')->setWidth(10);
                    $sheet->getColumnDimension('E')->setWidth(10);
                    $sheet->getColumnDimension('F')->setWidth(10);
                    $sheet->getColumnDimension('G')->setWidth(10);
                    $c = 8;
                    foreach ($attrList as $attr) {
                        $sheet->setCellValueExplicitByColumnAndRow($c, 1, $attr->name, DataType::TYPE_STRING);
                        $c += 1;
                    }
                    $r = 2;
                    /** @var CardBill $bill */
                    foreach ($query->each() as $bill) {
                        if ($bill->package_id != $package->id) {
                            continue;
                        }
                        $sheet->setCellValueExplicitByColumnAndRow(1, $r, $bill->card->iccid, DataType::TYPE_STRING);
                        $sheet->setCellValueExplicitByColumnAndRow(2, $r, $bill->card->msisdn, DataType::TYPE_STRING);
                        $sheet->setCellValueExplicitByColumnAndRow(3, $r, $user->username, DataType::TYPE_STRING);
                        $sheet->setCellValueExplicitByColumnAndRow(4, $r, $package->name, DataType::TYPE_STRING);
                        $sheet->setCellValueExplicitByColumnAndRow(5, $r, $userPackage->cost_price, DataType::TYPE_NUMERIC);
                        $sheet->setCellValueExplicitByColumnAndRow(6, $r, $bill->data/1024/1024, DataType::TYPE_NUMERIC);
                        $sheet->setCellValueExplicitByColumnAndRow(7, $r, $bill->sms, DataType::TYPE_NUMERIC);
                        $sheet->setCellValueExplicitByColumnAndRow(8, $r, $bill->voice, DataType::TYPE_NUMERIC);
                        $c = 8;
                        foreach ($attrList as $attr) {
                            $value = CardAttrValue::findOne(['uid' => $user->id, 'card_id' => $bill->card_id, 'attr_id' => $attr->id]);
                            if (!empty($value)) {
                                $sheet->setCellValueExplicitByColumnAndRow($c, $r, $value->value, DataType::TYPE_STRING);
                            }
                            $c += 1;
                        }
                        $r += 1;
                    }
                }
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="card_bill_' . date('Y-m-d') . '.xls"');
                header('Cache-Control: max-age=0');
                $excelWriter = IOFactory::createWriter($excel, 'Xls');
                $excelWriter->save('php://output');
            } catch (Exception $e) {
                Yii::error('导出Excel文件时出错：' . $e->getMessage());
                Yii::error($e->getTraceAsString());
                echo $e->getMessage();
            }
            die();
        }
        $pagination = new Pagination(['totalCount' => $query->count()]);
        $query->orderBy('id DESC');
        $query->offset($pagination->offset)->limit($pagination->limit);
        $billList = $query->all();

        return $this->render('bill_list', [
            'uid' => $this->user->id,
            'attrList' => $attrList,
            'billList' => $billList,
            'pagination' => $pagination,
        ]);
    }

}
